﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data;
using System.Collections;

namespace ChatQQServer
{
    class DBcon
    {
        SqlConnection con;
        SqlCommand cmd;
        SqlDataReader read;
        public DBcon()
        {
            con = new SqlConnection("server=localhost;uid=sa;pwd=sa;database=ChatQQ");
            con.Open();
        }
        public bool login(string uid,string pwd)
        {
            bool yes = false;
            cmd=new SqlCommand("select * from usertable where uid="+uid+" and upwd='"+pwd+"'",con);
            read = cmd.ExecuteReader();
            if (read.HasRows)
            {
                yes = true;
            }
            read.Close();
            return yes;
        }

        public string myself(string uid)
        {
            string myself="";
            string nice = "";
            string img = "";
            cmd = new SqlCommand("select uname ,uhead from usertable where uid=" + uid + "", con);
            read = cmd.ExecuteReader();
            if (read.Read())
            {
                nice=read.GetValue(0).ToString();
                img = read.GetValue(1).ToString();
            }
            myself = nice + "*" + img + "&";
            read.Close();
            return myself;
        }

        public String ferencemessage(string uid)
        {
            string fid = "";
            ArrayList lst = new ArrayList();
            cmd = new SqlCommand("select f.ffuid,u.uname,u.uhead,u.ustate from usertable as u inner join ferences as f on (f.ffuid=u.uid) where f.fpuid=" + uid + "", con);
            read = cmd.ExecuteReader();
            String temp = null;
            while (read.Read())
            {
                temp = read.GetValue(0) + "*" + read.GetValue(1) + "*" + read.GetValue(2) + "*" + read.GetValue(3);
                lst.Add(temp);
            }
            foreach (string  var in lst)
            {
                fid = fid + var + "&";
            }
            read.Close();
            fid = fid.TrimEnd(new char[] { '&' });
            return fid;
        }

        public string ferenceonline(string uid)
        {
            string ference = "";
            string temp = null;
            ArrayList lst = new ArrayList();
            cmd = new SqlCommand("select f.ffuid,u.uname,u.uhead,u.ustate from usertable as u inner join ferences as f on (f.ffuid=u.uid) where f.fpuid=" + uid + "", con);
            read = cmd.ExecuteReader();
            while (read.Read())
            {
                temp = read.GetValue(0) + "*" + read.GetValue(1) + "*" + read.GetValue(2) + "*" + read.GetValue(3);
                lst.Add(temp);
            }
            foreach (string var in lst)
            {
                ference = ference + var + "&";
            }
            read.Close();
            ference = ference.TrimEnd(new char[] { '&' });
            return ference;
        }
        public void online(string uid,int state)
        {
            cmd = new SqlCommand("update usertable set ustate=" + state + " where uid=" + uid + "", con);
            cmd.ExecuteNonQuery();
        }
        public void notonline(ArrayList lst)
        {
            string alluser = null;
            foreach (string var in lst)
            {
                alluser = alluser  + var+ ",";
            }
            if (alluser==null)
            {
                alluser = "00000";
            }
            alluser = alluser.TrimEnd(new char[] { ','});
            cmd = new SqlCommand("update usertable set ustate=3 where uid not in ("+alluser+") and ustate=1",con);
            cmd.ExecuteNonQuery();
        }
        public void delference(string uid,string duid)
        {
            cmd = new SqlCommand("delete  ferences where fpuid=" + uid + " and ffuid="+duid+"", con);
            cmd.ExecuteNonQuery();
        }
    }
}
